package es.xpt.ysf.commons.issues;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;

import com.thoughtworks.xstream.XStream;

import es.xpt.ysf.commons.issues.model.Issue;
import es.xpt.ysf.commons.issues.model.IssueHistory;
import es.xpt.ysf.commons.issues.model.IssueSubscribers;
import es.xpt.ysf.commons.issues.model.IssueValue;
import es.xpt.ysf.commons.issues.model.Issue.IssuePriority;
import es.xpt.ysf.commons.issues.model.Issue.IssueResolution;
import es.xpt.ysf.commons.issues.model.Issue.IssueStatus;
import es.xpt.ysf.commons.issues.model.Issue.IssueType;
import es.xpt.ysf.commons.issues.model.IssueSubscribers.CommunicationMedia;
import es.xpt.ysf.commons.jaas.JaasDAOFactory;
import es.xpt.ysf.commons.jaas.RolesDAO;
import es.xpt.ysf.dao.Connection;
import es.xpt.ysf.dao.DAOErrors;
import es.xpt.ysf.dao.DAOException;
import es.xpt.ysf.dao.DbDAO;
import es.xpt.ysf.dao.PreparedStatement;
import es.xpt.ysf.dao.Statement;
import es.xpt.ysf.jaas.YSFPrincipal;
import es.xpt.ysf.logging.Log;
import es.xpt.ysf.logging.LogManager;
import es.xpt.ysf.model.Item;
import es.xpt.ysf.utils.TimeUtils;
import es.xpt.ysf.utils.XMLSerializeUtils;


public class IssueDAO extends DbDAO<IssueValue> {
	private static final String ISSUES_FIELDS = 
		"ISU.id ISU_id, ISU.type ISU_type, ISU.date ISU_date, ISU.priority ISU_priority, ISU.status ISU_status, ISU.resolution ISU_resolution, "  +
		"ISU.entity ISU_entity, ISU.entityId ISU_entityId, ISU.businessArea ISU_businessArea, ISU.parameters ISU_parameters, " +
		"ISU.issueClass ISU_issueClass, ISU.user ISU_user";
	private static final String HISTORY_FIELDS = 
		"HIS.id HIS_id, HIS.issue HIS_issue, HIS.date HIS_date, HIS.action HIS_action, HIS.inputs HIS_inputs, " + 
		"HIS.outputs HIS_outputs, HIS.user HIS_user";
	private final String HISTORY_TABLE = "ysf_cb_issues_history HIS";
	private final String SUBSCRIBER_TABLE = "ysf_cb_issues_subscriber SUB";
	
	private Log log;

	public IssueDAO(Connection c, YSFPrincipal principal, DAOErrors errors) {
		super(c, principal, errors);
		log = LogManager.getLog(IssueDAO.class);
	}

	
	@Override
	protected IssueValue populateBean(ResultSet rs) throws SQLException {
	
		IssueValue issueValue = new IssueValue();
		
		issueValue.setId(rs.getString("ISU_id"));
		issueValue.setDate(rs.getTimestamp("ISU_date"));
		issueValue.setPriority(IssuePriority.valueOf(rs.getString("ISU_priority")));
		issueValue.setStatus(IssueStatus.valueOf(rs.getString("ISU_status")));
		String reso = rs.getString("ISU_resolution");
		String enti = rs.getString("ISU_entity");
		String entiId = rs.getString("ISU_entityId");
		String params = rs.getString("ISU_parameters");
		if (reso != null) {
			issueValue.setResolution(IssueResolution.valueOf(reso));	
		}
		if (enti != null) {
			issueValue.setEntity(enti);
		}
		if (entiId != null) {
			issueValue.setEntityId(entiId);	
		}
		issueValue.setBusinessArea(rs.getString("ISU_businessArea"));
		
		if (params != null && !params.isEmpty()) {
			XStream xstream = XMLSerializeUtils.configureEnvironment();
			issueValue.setParameters((Object[])xstream.fromXML(params));
			XMLSerializeUtils.reciclateXStream(xstream);
		}
		
		try {
			Issue issue = (Issue)Class.forName(rs.getString("ISU_issueClass")).newInstance();
			issue.setType(IssueType.valueOf(rs.getString("ISU_type")));
			issueValue.setIssue(issue);
		// TODO: Revisar que hacer en los catch
		} catch (ClassNotFoundException e) {
			log.error("ClassNotFoundException for issue class " + rs.getString("ISU_issueClass"));
		} catch (IllegalAccessException e) {
			log.error("IllegalAccessException for issue class " + rs.getString("ISU_issueClass"));
		} catch (InstantiationException e) {
			log.error("InstantiationException for issue class " + rs.getString("ISU_issueClass"));
		}
		issueValue.setUser(rs.getString("ISU_user"));
		
		return issueValue;
	}
	
	public void addIssue(IssueValue valueObject) throws DAOException {
		String statement = "INSERT INTO ysf_cb_issues" +  
			" (id,type,date,priority,status,resolution,entity,entityId,businessArea,parameters,issueClass,user) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
			
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			int i = 1;
			stmt.setString(i++, valueObject.getId());
			setCommonFields(i, stmt, valueObject);
			stmt.executeUpdate();
		} catch (SQLException se) {
			log.error("Error adding issue: " + se.toString());
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	public IssueValue getIssue(String id) throws DAOException {
		String selectStatement = "SELECT "+ ISSUES_FIELDS + ", " + HISTORY_FIELDS
			+ " FROM ysf_cb_issues ISU left outer join " + HISTORY_TABLE + " on ISU.id=HIS.issue"
			+ " WHERE ISU.id = '" + id + "'";

		Statement stmt = null;
		try {
			
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectStatement);
			if ( rs.next() ) {
				IssueValue is = populateBean(rs);
				String hisId = rs.getString("HIS_id");
				if ( hisId!=null ) {
					List<IssueHistory> issueHistoryList = new ArrayList<IssueHistory>();
					do {
						IssueHistory issueHistory = new IssueHistory();
						issueHistory.setId(hisId);
						issueHistory.setDate(rs.getTimestamp("HIS_date"));
						issueHistory.setAction(rs.getString("HIS_action"));
						issueHistory.setInputs(rs.getString("HIS_inputs"));
						issueHistory.setOutputs(rs.getString("HIS_outputs"));
						issueHistory.setUser(rs.getString("HIS_user"));
						issueHistoryList.add(issueHistory);
					} while (rs.next());
					is.setIssueHistoryList(issueHistoryList);
				}
				
				return is;
			}
			return null;
		} catch (SQLException se) {
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public void updateIssue(IssueValue valueObject) throws DAOException {
		String statement = "UPDATE ysf_cb_issues SET type = ?, date = ?, priority = ?, status = ?, resolution = ?," +
			" entity = ?, entityId = ?, businessArea = ?, parameters = ?, issueClass = ?, user = ?" +
			" WHERE id = ? ";
		
		PreparedStatement stmt = null;
		try {
			
			stmt = con.prepareStatement(statement);
			int i = setCommonFields(1, stmt, valueObject);
			stmt.setString(i, valueObject.getId());
			stmt.executeUpdate();
		} catch (SQLException se) {
			log.error("Error updating issue: " + se.toString());
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	public void addHistoryItem(String issueId, IssueHistory history) throws DAOException {
		String statement = "INSERT INTO ysf_cb_issues_history " + 
			" (id, issue, date, action, inputs, outputs, user) " +
			"VALUES (?, ?, ?, ?, ?, ?, ?)";
		
		PreparedStatement stmt = null;
		try {
			stmt = con.prepareStatement(statement);
			int i = 1;
			stmt.setString(i++, history.getId());
			stmt.setString(i++, issueId);
			stmt.setTimestamp(i++, new Timestamp(history.getDate().getTime()));
			stmt.setString(i++, history.getAction());
			if (history.getInputs() != null) {
				stmt.setString(i++, history.getInputs());	
			} else {
				stmt.setNull(i++, java.sql.Types.VARCHAR);	
			}
			if (history.getOutputs() != null) {
				stmt.setString(i++, history.getOutputs());	
			} else {
				stmt.setNull(i++, java.sql.Types.VARCHAR);	
			}
			stmt.setString(i++, history.getUser());

			stmt.executeUpdate();
		} catch (SQLException se) {
			log.error("Error adding history item: " + se.toString());
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}	
	}
	
	public void deleteSubscribers(String businessArea) throws DAOException {
		String deleteStatement = "DELETE FROM ysf_cb_issues_subscriber WHERE businessArea = '"
			+ businessArea + "'";
	
		PreparedStatement stmt = null;
		try {
			// Delete
			stmt = con.prepareStatement(deleteStatement);
			stmt.executeUpdate();
		} catch (SQLException se) {
			log.error("Error deleting subscribers: " + se.toString());
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	public void addSubscribers(String businessArea, IssueSubscribers subscribers) throws DAOException {
		String insertStatement = "INSERT INTO ysf_cb_issues_subscriber" +  
			" (businessArea, user, role, advise) VALUES ('" + 
			businessArea + "', ?, ?, ?)";
	
		PreparedStatement stmt = null;
		try {
			// Insert
			stmt = con.prepareStatement(insertStatement);
			for (YSFPrincipal principal: subscribers.getUsers()) {
				stmt.setString(1, principal.getName());
				stmt.setNull(2, java.sql.Types.VARCHAR);
				CommunicationMedia media = subscribers.getCommunicationMedia().get(principal);
				if (media != null) {
					stmt.setString(3, media.toString());
				} else {
					stmt.setNull(3, java.sql.Types.VARCHAR);
				}
				stmt.addBatch();
			}
			for (Item role: subscribers.getRoles()) {
				stmt.setNull(1, java.sql.Types.VARCHAR);	
				stmt.setString(2, role.getCode());
				stmt.setNull(3, java.sql.Types.VARCHAR);
				stmt.addBatch();
			}
			stmt.executeBatch();
		} catch (SQLException se) {
			log.error("Error adding subscribers: " + se.toString());
			throw new DAOException(se, errors);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}

	public boolean existsOpenedIssue(Issue issue, String entity, String entityId, String businessArea, Object[] parameters)  throws DAOException {
		XStream xstream = XMLSerializeUtils.configureEnvironment();
		String parametersStr = xstream.toXML(parameters);
		
		String selectStatement = "SELECT ISU.status ISU_status FROM ysf_cb_issues ISU WHERE" +
			" ISU.issueClass = '" + issue.getClass().getName() + 
			"' and ISU.type = '" + issue.getType() + "'" +
			( entity!=null ? " and ISU.entity = '" + entity + "'": "" ) +
			( entityId!=null ? " and ISU.entityId = '" + entityId + "'": "" ) +
			" and ISU.businessArea = '" + businessArea +
			"' and ISU.status != '" + IssueStatus.CLOSED + 
			"' and ISU.status != '" + IssueStatus.RESOLVED +
			"' and ISU.parameters = " + toSQLString(parametersStr); 
	
		Statement stmt = null;
		try {
			stmt = con.createStatement();
			ResultSet rs = stmt.executeQuery(selectStatement);
			return rs.next();
		} catch (SQLException se) {
			throw new DAOException(se);
		} finally {
			if (stmt != null)
				stmt.close();
		}
	}
	
	private String toSQLString(String source) {
		String result = source.replaceAll("\\r", "',CHAR(13),'").replaceAll("\\n", "',CHAR(10),'").replaceAll("\\t", "',CHAR(9),'");
		return "concat('" + result + "')";
	}
	
	public List<IssueValue> searchIssue ( IssueSearchParams searchParameters ) throws DAOException {
		JaasDAOFactory f = null;
		List<Item> subscribedUserRoles = new ArrayList<Item>();
		String selectStatement = "SELECT DISTINCT " + ISSUES_FIELDS + " FROM ysf_cb_issues ISU";
		
		if (!searchParameters.getSubscribedRoles().isEmpty() || 
				(searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty())) {
			selectStatement += " INNER JOIN " + SUBSCRIBER_TABLE + " ON ISU.businessArea = SUB.businessArea";
		}
		
		selectStatement += " WHERE 1=1"; 
			
		if (searchParameters.getType() != null) {
			selectStatement += " AND ISU.type = ?";
		}
		if (searchParameters.getStatus() != null && !searchParameters.getStatus().isEmpty()) {
			selectStatement += " AND (";
			for ( IssueStatus is : searchParameters.getStatus() )
				selectStatement += " ISU.status = '" + is.name() + "' or";
			selectStatement = selectStatement.substring(0, selectStatement.length()-2) + ")";
		}	
		if (searchParameters.getResolution() != null) {
			selectStatement += " AND ISU.resolution = ?";
		}
		if (searchParameters.getEntity() != null && !searchParameters.getEntity().isEmpty()) {
			selectStatement += " AND ISU.entity = ?";
		}
		if (searchParameters.getEntityId() != null && !searchParameters.getEntityId().isEmpty()) {
			selectStatement += " AND ISU.entityId = ?";
		}
		if (searchParameters.getBusinessArea() != null && !searchParameters.getBusinessArea().isEmpty()) {
			selectStatement += " AND ISU.businessArea = ?";
		}
		if (searchParameters.getParameters() != null && !searchParameters.getParameters().isEmpty()) {
			selectStatement += " AND ISU.parameters like '%" + searchParameters.getParameters() + "%'";
		}
		
		if (searchParameters.getPriority() != null) {
			selectStatement += " AND ISU.priority = ?";
		}
		if (searchParameters.getUser() != null) {
			selectStatement += " AND ISU.user = ?";
		}
		if (searchParameters.getFromDate() != null) {
			selectStatement += " AND ISU.date >= ?";
		}
		if (searchParameters.getToDate() != null) {
			selectStatement += " AND ISU.date < ?";
		}
		if ( searchParameters.getClassName()!=null ) {
			selectStatement += " AND ISU.issueClass = ?";
		}
		
		if (!searchParameters.getSubscribedRoles().isEmpty() || 
				(searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty())) {
			selectStatement += (" AND (1=2 ");
			if (!searchParameters.getSubscribedRoles().isEmpty()) {
				for (String role : searchParameters.getSubscribedRoles()) {
					if (role != null && !role.isEmpty()) {
						selectStatement += " OR SUB.role = ?";
					}
				}	
			}
			if (searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty()) {
				selectStatement += " OR SUB.user = ?";
				
				f = new JaasDAOFactory(principal);
				RolesDAO rolesDao = f.getRolesDAO();
				subscribedUserRoles = rolesDao.getUserRoles(searchParameters.getSubscribedUser(), null);
				for (Item role : subscribedUserRoles) {
					if (role != null && role.getCode() !=null && !role.getCode().isEmpty()) {
						selectStatement += " OR SUB.role = ?";
					}
				}	
			}
			selectStatement += ")";
		}
		selectStatement += " ORDER BY ISU.date desc, ISU_priority " 
			+ getLimitStatement(-1,searchParameters.getResultsNumber());
		

		PreparedStatement stmt = null;
        try {
        	int i = 1;
        	stmt = con.prepareStatement(selectStatement);
        	
        	if (searchParameters.getType() != null) {
        		stmt.setString(i++, searchParameters.getType().toString());
	   		}
	   		if (searchParameters.getResolution() != null) {
	   			stmt.setString(i++, searchParameters.getResolution().toString());
	   		}
	   		if (searchParameters.getEntity() != null && !searchParameters.getEntity().isEmpty()) {
	   			stmt.setString(i++, searchParameters.getEntity());
	   		}
	   		if (searchParameters.getEntityId() != null && !searchParameters.getEntityId().isEmpty()) {
	   			stmt.setString(i++, searchParameters.getEntityId());
	   		}
	   		if (searchParameters.getBusinessArea() != null && !searchParameters.getBusinessArea().isEmpty()) {
	   			stmt.setString(i++, searchParameters.getBusinessArea());
	   		}
	   		if (searchParameters.getPriority() != null) {
	   			stmt.setString(i++, searchParameters.getPriority().toString());
	   		}
	   		if (searchParameters.getUser() != null) {
	   			stmt.setString(i++, searchParameters.getUser());
	   		}
	   		if (searchParameters.getFromDate() != null) {
	   			stmt.setDate(i++, searchParameters.getFromDate());
	   		}
	   		if (searchParameters.getToDate() != null) {
	   			stmt.setDate(i++, TimeUtils.getAfterDate(searchParameters.getToDate()));
	   		}
	   		if ( searchParameters.getClassName() != null ) {
	   			stmt.setString(i++, searchParameters.getClassName());
	   		}
	   		
	   		if (!searchParameters.getSubscribedRoles().isEmpty() || 
					(searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty())) {
				if (!searchParameters.getSubscribedRoles().isEmpty()) {
					for (String roleCode : searchParameters.getSubscribedRoles()) {
						if (roleCode != null && !roleCode.isEmpty()) {
							stmt.setString(i++, roleCode);				
						}
					}	
				}
				if (searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty()) {
					stmt.setString(i++, searchParameters.getSubscribedUser());
				}
				
				if (searchParameters.getSubscribedUser() != null && !searchParameters.getSubscribedUser().isEmpty()) {
					for (Item role : subscribedUserRoles) {
						if (role != null && role.getCode()!=null && !role.getCode().isEmpty()) {
							stmt.setString(i++, role.getCode());
						}
					}	
				}
			}
	   		return listQuery(stmt);
	   	} catch (SQLException se){
        	throw new DAOException(se);
        } finally {
        	if (f != null)
        		f.release();
        	if (stmt != null) 
        		stmt.close();
        }  
	}
	
	public List<String> getBusinessAreas() throws DAOException {
		String selectStatement = "SELECT DISTINCT businessArea FROM ysf_cb_issues ORDER BY businessArea";
		
		Statement stmt = null;
	    try {
	    	stmt = con.createStatement();
	    	ResultSet rs = stmt.executeQuery(selectStatement);
	    	ArrayList<String> businessAreas = new ArrayList<String>();
	    	while ( rs.next() ) {
	    		businessAreas.add(rs.getString("businessArea"));
	    	}
	    	return businessAreas;
	    } catch (SQLException se){
	    	throw new DAOException(se);
	    } finally {
	    	if ( stmt!=null ) stmt.close();
	    }	
	}
	
	public List<String> getSubscribedRoles() throws DAOException {
		String selectStatement = "SELECT DISTINCT role FROM "+ SUBSCRIBER_TABLE + " ORDER BY role";
		
		Statement stmt = null;
	    try {
	    	stmt = con.createStatement();
	    	ResultSet rs = stmt.executeQuery(selectStatement);
	    	ArrayList<String> roles = new ArrayList<String>();
	    	while ( rs.next() ) {
	    		roles.add(rs.getString("role"));
	    	}
	    	return roles;
	    } catch (SQLException se){
	    	throw new DAOException(se);
	    } finally {
	    	if ( stmt!=null ) stmt.close();
	    }	
	}
	
	public List<String> getSubscribedUsers() throws DAOException {
		String selectStatement = "SELECT DISTINCT user FROM "+ SUBSCRIBER_TABLE + " ORDER BY user";
		
		Statement stmt = null;
	    try {
	    	stmt = con.createStatement();
	    	ResultSet rs = stmt.executeQuery(selectStatement);
	    	ArrayList<String> roles = new ArrayList<String>();
	    	while ( rs.next() ) {
	    		roles.add(rs.getString("role"));
	    	}
	    	return roles;
	    } catch (SQLException se){
	    	throw new DAOException(se);
	    } finally {
	    	if ( stmt!=null ) stmt.close();
	    }	
	}
	
	private int setCommonFields(int i, PreparedStatement stmt, IssueValue valueObject) throws SQLException {
		stmt.setString(i++, valueObject.getIssue().getType().toString());
		stmt.setTimestamp(i++, new Timestamp(valueObject.getDate().getTime()));
		stmt.setString(i++, valueObject.getPriority().toString());
		stmt.setString(i++, valueObject.getStatus().toString());
		if (valueObject.getResolution() != null) {
			stmt.setString(i++, valueObject.getResolution().toString());	
		} else {
			stmt.setNull(i++, java.sql.Types.VARCHAR);	
		}
		if (valueObject.getEntity() != null) {
			stmt.setString(i++, valueObject.getEntity());
		} else {
			stmt.setNull(i++, java.sql.Types.VARCHAR);
		}
		if (valueObject.getEntityId() != null) {
			stmt.setString(i++, valueObject.getEntityId());	
		} else {
			stmt.setNull(i++, java.sql.Types.VARCHAR);	
		}
		stmt.setString(i++, valueObject.getBusinessArea());
		
		if (valueObject.getParameters() != null) {
			//XStream xstream = XMLSerializeUtils.configureEnvironmentIncludeParams(valueObject.class, valueObject.class, "parameters");
			XStream xstream = XMLSerializeUtils.configureEnvironment();
			stmt.setString(i++, xstream.toXML(valueObject.getParameters()));
		} else {
			stmt.setNull(i++, java.sql.Types.VARCHAR);
		}
			
		stmt.setString(i++, valueObject.getIssue().getClass().getName());
		stmt.setString(i++, valueObject.getUser());
		return i;
	}
}
